import openpyxl as xl


class ReadExcel:
    def __init__(self, file=None):
        if not file:
            print('请先创建一个Excel文档')
        else:
            self.wb = xl.load_workbook(filename=file, read_only=True)

    def read_to_dict(self, sheet_name):
        """
        将Excel中内容转换为key:value的形式，并将所有结果添加到一个列表内
        :param sheet_name: sheet页名称，默认为空
        :return: 列表，每个元素为一条用例
        """
        ws = self.wb[sheet_name]
        l = []
        res = []
        for row in ws.values:
            l.append(row)
        for element in l[1:]:
            d = {}
            r = list(zip(l[0], element))
            for j in r:
                # 如果值为字典或列表，将字符串转换为对应的格式
                if isinstance(j[1], str) and (j[1].startswith('{') or j[1].startswith('[')):
                    d[j[0]] = eval(j[1])
                # 如果值是以#号开头的数字，则将其变为字符串形式的数字
                elif isinstance(j[1], str) and j[1].startswith('#'):
                    d[j[0]] = j[1][1:]
                # 如果值为字符串或数字
                elif j[1] and (isinstance(j[1], int) or isinstance(j[1], str) or isinstance(j[1], float)):
                    d[j[0]] = j[1]
                # 如果value为None，则该key不会添加到入参中（模拟参数中的不传值）
                elif j[1] is None:
                    continue
            res.append(d)
        return res


if __name__ == '__main__':
    excel = ReadExcel(file=r'C:\Users\test\Desktop\0824.xlsx')
    r = excel.read_to_dict('Sheet1')
    for i in r:
        print(i)
